package com.husd.framework.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;


public class MyExcelUtil {
    /**
     * 创建一个新的excel文件
     * 返回数据的最后一行行号
     *
     * @param fileName
     * @param head
     * @param data
     * @return
     */
    public static int createExcel(String fileName, List<String> head, ArrayList<ArrayList<Object>> data) throws Exception {
        // 导出的excel,全文件名
        Cell cell;
        Row row;
        int lastRowNumber = 1;
        /// -> excel到处逻辑
        // 获取SXSSFWorkbook实例
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100);
        Sheet sheet = sxssfWorkbook.createSheet("Sheet1");
        // 冻结最左边的两列、冻结最上面的一行
        // 即：滚动横向滚动条时，左边的第一列固定不动;滚动纵向滚动条时，上面的第一行固定不动。
        sheet.createFreezePane(1, 1);
        // 设置并获取到需要的样式
        XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
        XSSFCellStyle xssfCellStyle = getAndSetXSSFCellStyle(sxssfWorkbook);
        // 创建第一行,作为header表头
        Row header = sheet.createRow(0);
        // 循环创建header单元格(根据实际情况灵活创建即可)
        for (int cellnum = 0; cellnum < head.size(); cellnum++) {
            cell = header.createCell(cellnum);
            cell.setCellStyle(xssfCellStyleHeader);
            cell.setCellValue(head.get(cellnum));
        }
        // 遍历创建行,导出数据
        for (int rownum = 1; rownum <= data.size(); rownum++) {
            row = sheet.createRow(rownum);
            // 循环创建单元格
            ArrayList<Object> columnList = data.get(rownum - 1);
            final int columnSize = columnList.size();
            for (int cellnum = 0; cellnum < columnSize; cellnum++) {
                cell = row.createCell(cellnum);
                cell.setCellStyle(xssfCellStyle);
                Object excelCellValue = columnList.get(cellnum);
                if (excelCellValue == null) {
                    cell.setCellValue("");
                } else {
                    String tempStrVal = excelCellValue.toString();
                    if (tempStrVal.indexOf("0") != 0 &&
                            StringUtils.isNumeric(tempStrVal)) {
                        cell.setCellValue(Double.parseDouble(tempStrVal));
                    } else {
                        cell.setCellValue(tempStrVal);
                    }
                }
                lastRowNumber = rownum;
            }
        }
        // 在后面设置sheet
        setSheet(sheet, head.size());
        FileOutputStream fileOutputStream = new FileOutputStream(fileName);
        sxssfWorkbook.write(fileOutputStream);
        sxssfWorkbook.dispose();
        fileOutputStream.flush();
        fileOutputStream.close();
        return ++lastRowNumber;
    }

    /**
     * 末尾追加内容到已有的excel文件
     *
     * @param fileName
     * @param data
     */
    public static int appendExcel(String fileName, ArrayList<ArrayList<Object>> data, Integer offset) throws Exception {
        int lastRowNumber = 1;
        Cell cell;
        Row row;
        FileInputStream fileInputStream = null;
        fileInputStream = new FileInputStream(fileName);
        SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook(fileInputStream), 100);
        Sheet sheet = workbook.getSheet("Sheet1");
        XSSFCellStyle xssfCellStyle = getAndSetXSSFCellStyle(workbook);
        for (int rownum = offset; rownum < offset + data.size(); rownum++) {
            row = sheet.createRow(rownum);
            for (int cellnum = 0; cellnum < data.get(rownum - offset).size(); cellnum++) {
                cell = row.createCell(cellnum);
                cell.setCellStyle(xssfCellStyle);
                if (data.get(rownum - offset).get(cellnum) == null) {
                    cell.setCellValue("");
                } else if (data.get(rownum - offset).get(cellnum).toString().indexOf("0") != 0 && StringUtils.isNumeric(data.get(rownum - offset).get(cellnum).toString())) {
                    cell.setCellValue(Double.parseDouble(data.get(rownum - offset).get(cellnum).toString()));
                } else {
                    cell.setCellValue(data.get(rownum - offset).get(cellnum).toString());
                }
            }
            lastRowNumber = rownum;
        }
        FileOutputStream fileOutputStream = new FileOutputStream(fileName);
        workbook.write(fileOutputStream);
        workbook.dispose();
        fileOutputStream.flush();
        //先打开的流后关闭 后打开的流先关闭
        fileOutputStream.close();
        fileInputStream.close();
        return ++lastRowNumber;
    }

    /**
     * 设置sheet
     */
    private static void setSheet(Sheet sheet, int columnSize) {
        // 设置各列宽度(单位为:字符宽度的1/256)
        for (int i = 0; i < columnSize; i++) {
            sheet.setColumnWidth(i, 1 * 256);
        }
    }

    /**
     * 获取并设置header样式
     */
    private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        Font font = sxssfWorkbook.createFont();
//        if (sheetConfigure.getIsHeadBold() == null) {
//            sheetConfigure.setIsHeadBold(false);
//        }
        // 字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体粗细
        font.setBold(true);
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }

    /**
     * 获取并设置样式
     */
    private static XSSFCellStyle getAndSetXSSFCellStyle(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        /*xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());*/
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }

    public static int createExcelWithThis(String fileName, List<String> head,
                                           WriteBigExcelTest writeBigExcelTest) throws IOException {

        // 导出的excel,全文件名
        Cell cell;
        Row row;
        int lastRowNumber = 1;
        /// -> excel到处逻辑
        // 获取SXSSFWorkbook实例
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(10);
        Sheet sheet = sxssfWorkbook.createSheet("Sheet1");
        // 冻结最左边的两列、冻结最上面的一行
        // 即：滚动横向滚动条时，左边的第一列固定不动;滚动纵向滚动条时，上面的第一行固定不动。
        sheet.createFreezePane(1, 1);
        // 设置并获取到需要的样式
        XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
        XSSFCellStyle xssfCellStyle = getAndSetXSSFCellStyle(sxssfWorkbook);
        // 创建第一行,作为header表头
        Row header = sheet.createRow(0);
        // 循环创建header单元格(根据实际情况灵活创建即可)
        for (int cellnum = 0; cellnum < head.size(); cellnum++) {
            cell = header.createCell(cellnum);
            cell.setCellStyle(xssfCellStyleHeader);
            cell.setCellValue(head.get(cellnum));
        }

        int page = 1;
        int totalPage = 1000;
        int rownum = 0;

        while(page < totalPage) {
            ArrayList<ArrayList<Object>> data = writeBigExcelTest.mockList(500,100);
            // 遍历创建行,导出数据
            for (ArrayList<Object> columnList: data) {
                rownum++;
                row = sheet.createRow(rownum);
                // 循环创建单元格
                final int columnSize = columnList.size();
                for (int cellnum = 0; cellnum < columnSize; cellnum++) {
                    cell = row.createCell(cellnum);
                    cell.setCellStyle(xssfCellStyle);
                    Object excelCellValue = columnList.get(cellnum);
                    if (excelCellValue == null) {
                        cell.setCellValue("");
                    } else {
                        String tempStrVal = excelCellValue.toString();
                        if (tempStrVal.indexOf("0") != 0 &&
                                StringUtils.isNumeric(tempStrVal)) {
                            cell.setCellValue(Double.parseDouble(tempStrVal));
                        } else {
                            cell.setCellValue(tempStrVal);
                        }
                    }
                    lastRowNumber = rownum;
                }
            }
            data = null;
            System.out.println("第：" + page + "页数据已经写入完成....");
            page++;
        }
        // 在后面设置sheet
        setSheet(sheet, head.size());
        FileOutputStream fileOutputStream = new FileOutputStream(fileName);
        sxssfWorkbook.write(fileOutputStream);
        sxssfWorkbook.dispose();
        fileOutputStream.flush();
        fileOutputStream.close();
        return ++lastRowNumber;
    }
}
